const express = require('express');
const router = express.Router();
const pool = require('../db');
const authMiddleware = require('../middleware/auth');

// GET /api/cities
// If page & limit provided returns { cities: [...], total }
// Otherwise returns { cities: [...] }
router.get('/cities', authMiddleware, async (req, res) => {
  try {
    const page = req.query.page ? Math.max(1, parseInt(req.query.page, 10) || 1) : null;
    const limit = req.query.limit ? Math.max(1, parseInt(req.query.limit, 10) || 10) : null;
    const offset = page && limit ? (page - 1) * limit : null;

    const allowedSorts = {
      city_name: 'city_name',
      city_code: 'city_code',
      id: 'id'
    };
    const sortBy = typeof req.query.sort_by === 'string' ? req.query.sort_by : null;
    let sortOrder = typeof req.query.sort_order === 'string' ? req.query.sort_order.toLowerCase() : null;
    if (sortOrder !== 'asc' && sortOrder !== 'desc') sortOrder = null;

    let orderClause = 'id DESC';
    if (sortBy && allowedSorts[sortBy]) {
      orderClause = `${allowedSorts[sortBy]} ${sortOrder === 'asc' ? 'ASC' : 'DESC'}`;
    }

    let sql = `SELECT id, city_name, province, city_code, remark FROM atb_city ORDER BY ${orderClause}`;

    if (page && limit) {
      sql += ' LIMIT ? OFFSET ?';
      const [rows] = await pool.query(sql, [limit, offset]);
      const [countRows] = await pool.query('SELECT COUNT(*) AS total FROM atb_city');
      const total = (countRows && countRows[0] && countRows[0].total) ? countRows[0].total : 0;
      return res.json({ cities: rows, total });
    } else {
      const [rows] = await pool.query(sql);
      return res.json({ cities: rows });
    }
  } catch (err) {
    console.error('Failed to fetch cities:', err);
    res.status(500).json({ message: 'Server error' });
  }
});

module.exports = router;
